package com.tony.excel;

import com.alibaba.fastjson.JSONObject;
import com.tony.utils.ConvertUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * @author tony
 * @Date 2018/11/20
 */
public class JsonToExcel {
    public static void main(String[] args) throws Exception {
       String json = getJson();
       List<String> titles = Arrays.asList();
       List<Map<String,Object>> lists = JSONObject.parseObject(json, List.class);
        HSSFWorkbook workbook =  new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("账户信息");
        int row = 0;

        if(titles.size()>0){
            HSSFRow row0 = sheet.createRow(row++);
            int col = 0;
            for(String title:titles){
                row0.createCell(col).setCellValue(title.toString());
                col++;
            }
        }else{
            titles = new ArrayList<>();
            HSSFRow row0 = sheet.createRow(row++);
            int col = 0;
            Map<String,Object> ti = lists.get(0);
            for(String title:ti.keySet()){
                row0.createCell(col).setCellValue(title.toString());
                titles.add(title);
                col++;
            }
        }

        for(Map<String,Object> datas : lists){
            HSSFRow hssfRow = sheet.createRow(row);
            int col = 0;
            for(String title:titles){
                hssfRow.createCell(col).setCellValue(ConvertUtil.toStr(datas.get(title)));
                col++;
            }
            row++;
        }
        FileOutputStream fos = new FileOutputStream("D:\\test.xls");
        workbook.write(fos);
        System.out.println("-------END------");
    }

    private static String getJson() {
        return "[{\"TPS_SEQ\":8616908,\"APP_ID\":2119432,\"CUSTOMER_ID\":\"899792\",\"PRODUCT_ID\":\"156\",\"PERD_NO\":1,\"DUE_DT\":\"2015-12-28\",\"INSTM_AMT\":662.76,\"PRCP_AMT\":586.5,\"INT_AMT\":76.26,\"SCHED_AMT\":7450,\"SETL_PRCP_AMT\":0,\"SETL_INT_AMT\":0,\"SETL_STS\":\"N\",\"THD_BUS_ID\":\"16806859\",\"LAST_UP_TM\":1448964348000,\"LAST_UP_USR\":\"SYSTEM\",\"REMARK\":\"原还款计划ID:16806859\",\"SETL_DT\":null},{\"TPS_SEQ\":8616909,\"APP_ID\":2119432,\"CUSTOMER_ID\":\"899792\",\"PRODUCT_ID\":\"156\",\"PERD_NO\":2,\"DUE_DT\":\"2016-01-28\",\"INSTM_AMT\":662.76,\"PRCP_AMT\":592.75,\"INT_AMT\":70.01,\"SCHED_AMT\":6863.5,\"SETL_PRCP_AMT\":0,\"SETL_INT_AMT\":0,\"SETL_STS\":\"N\",\"THD_BUS_ID\":\"16806860\",\"LAST_UP_TM\":1448964348000,\"LAST_UP_USR\":\"SYSTEM\",\"REMARK\":\"原还款计划ID:16806860\",\"SETL_DT\":null},{\"TPS_SEQ\":8616910,\"APP_ID\":2119432,\"CUSTOMER_ID\":\"899792\",\"PRODUCT_ID\":\"156\",\"PERD_NO\":3,\"DUE_DT\":\"2016-02-28\",\"INSTM_AMT\":662.76,\"PRCP_AMT\":598.8,\"INT_AMT\":63.96,\"SCHED_AMT\":6270.75,\"SETL_PRCP_AMT\":0,\"SETL_INT_AMT\":0,\"SETL_STS\":\"N\",\"THD_BUS_ID\":\"16806861\",\"LAST_UP_TM\":1448964348000,\"LAST_UP_USR\":\"SYSTEM\",\"REMARK\":\"原还款计划ID:16806861\",\"SETL_DT\":null},{\"TPS_SEQ\":8616911,\"APP_ID\":2119432,\"CUSTOMER_ID\":\"899792\",\"PRODUCT_ID\":\"156\",\"PERD_NO\":4,\"DUE_DT\":\"2016-03-28\",\"INSTM_AMT\":662.76,\"PRCP_AMT\":604.91,\"INT_AMT\":57.85,\"SCHED_AMT\":5671.95,\"SETL_PRCP_AMT\":0,\"SETL_INT_AMT\":0,\"SETL_STS\":\"N\",\"THD_BUS_ID\":\"16806862\",\"LAST_UP_TM\":1448964348000,\"LAST_UP_USR\":\"SYSTEM\",\"REMARK\":\"原还款计划ID:16806862\",\"SETL_DT\":null},{\"TPS_SEQ\":8616912,\"APP_ID\":2119432,\"CUSTOMER_ID\":\"899792\",\"PRODUCT_ID\":\"156\",\"PERD_NO\":5,\"DUE_DT\":\"2016-04-28\",\"INSTM_AMT\":662.76,\"PRCP_AMT\":611.08,\"INT_AMT\":51.68,\"SCHED_AMT\":5067.04,\"SETL_PRCP_AMT\":0,\"SETL_INT_AMT\":0,\"SETL_STS\":\"N\",\"THD_BUS_ID\":\"16806863\",\"LAST_UP_TM\":1448964348000,\"LAST_UP_USR\":\"SYSTEM\",\"REMARK\":\"原还款计划ID:16806863\",\"SETL_DT\":null},{\"TPS_SEQ\":8616913,\"APP_ID\":2119432,\"CUSTOMER_ID\":\"899792\",\"PRODUCT_ID\":\"156\",\"PERD_NO\":6,\"DUE_DT\":\"2016-05-28\",\"INSTM_AMT\":662.76,\"PRCP_AMT\":617.31,\"INT_AMT\":45.45,\"SCHED_AMT\":4455.96,\"SETL_PRCP_AMT\":0,\"SETL_INT_AMT\":0,\"SETL_STS\":\"N\",\"THD_BUS_ID\":\"16806864\",\"LAST_UP_TM\":1448964348000,\"LAST_UP_USR\":\"SYSTEM\",\"REMARK\":\"原还款计划ID:16806864\",\"SETL_DT\":null},{\"TPS_SEQ\":8616914,\"APP_ID\":2119432,\"CUSTOMER_ID\":\"899792\",\"PRODUCT_ID\":\"156\",\"PERD_NO\":7,\"DUE_DT\":\"2016-06-28\",\"INSTM_AMT\":662.76,\"PRCP_AMT\":623.61,\"INT_AMT\":39.15,\"SCHED_AMT\":3838.65,\"SETL_PRCP_AMT\":0,\"SETL_INT_AMT\":0,\"SETL_STS\":\"N\",\"THD_BUS_ID\":\"16806865\",\"LAST_UP_TM\":1448964348000,\"LAST_UP_USR\":\"SYSTEM\",\"REMARK\":\"原还款计划ID:16806865\",\"SETL_DT\":null},{\"TPS_SEQ\":8616915,\"APP_ID\":2119432,\"CUSTOMER_ID\":\"899792\",\"PRODUCT_ID\":\"156\",\"PERD_NO\":8,\"DUE_DT\":\"2016-07-28\",\"INSTM_AMT\":662.76,\"PRCP_AMT\":629.97,\"INT_AMT\":32.79,\"SCHED_AMT\":3215.04,\"SETL_PRCP_AMT\":0,\"SETL_INT_AMT\":0,\"SETL_STS\":\"N\",\"THD_BUS_ID\":\"16806866\",\"LAST_UP_TM\":1448964348000,\"LAST_UP_USR\":\"SYSTEM\",\"REMARK\":\"原还款计划ID:16806866\",\"SETL_DT\":null},{\"TPS_SEQ\":8616916,\"APP_ID\":2119432,\"CUSTOMER_ID\":\"899792\",\"PRODUCT_ID\":\"156\",\"PERD_NO\":9,\"DUE_DT\":\"2016-08-28\",\"INSTM_AMT\":662.76,\"PRCP_AMT\":636.39,\"INT_AMT\":26.37,\"SCHED_AMT\":2585.07,\"SETL_PRCP_AMT\":0,\"SETL_INT_AMT\":0,\"SETL_STS\":\"N\",\"THD_BUS_ID\":\"16806867\",\"LAST_UP_TM\":1448964348000,\"LAST_UP_USR\":\"SYSTEM\",\"REMARK\":\"原还款计划ID:16806867\",\"SETL_DT\":null},{\"TPS_SEQ\":8616917,\"APP_ID\":2119432,\"CUSTOMER_ID\":\"899792\",\"PRODUCT_ID\":\"156\",\"PERD_NO\":10,\"DUE_DT\":\"2016-09-28\",\"INSTM_AMT\":662.76,\"PRCP_AMT\":642.88,\"INT_AMT\":19.88,\"SCHED_AMT\":1948.68,\"SETL_PRCP_AMT\":0,\"SETL_INT_AMT\":0,\"SETL_STS\":\"N\",\"THD_BUS_ID\":\"16806868\",\"LAST_UP_TM\":1448964348000,\"LAST_UP_USR\":\"SYSTEM\",\"REMARK\":\"原还款计划ID:16806868\",\"SETL_DT\":null},{\"TPS_SEQ\":8616918,\"APP_ID\":2119432,\"CUSTOMER_ID\":\"899792\",\"PRODUCT_ID\":\"156\",\"PERD_NO\":11,\"DUE_DT\":\"2016-10-28\",\"INSTM_AMT\":662.76,\"PRCP_AMT\":649.44,\"INT_AMT\":13.32,\"SCHED_AMT\":1305.8,\"SETL_PRCP_AMT\":0,\"SETL_INT_AMT\":0,\"SETL_STS\":\"N\",\"THD_BUS_ID\":\"16806869\",\"LAST_UP_TM\":1448964348000,\"LAST_UP_USR\":\"SYSTEM\",\"REMARK\":\"原还款计划ID:16806869\",\"SETL_DT\":null},{\"TPS_SEQ\":8616919,\"APP_ID\":2119432,\"CUSTOMER_ID\":\"899792\",\"PRODUCT_ID\":\"156\",\"PERD_NO\":12,\"DUE_DT\":\"2016-11-28\",\"INSTM_AMT\":662.76,\"PRCP_AMT\":656.07,\"INT_AMT\":6.69,\"SCHED_AMT\":656.36,\"SETL_PRCP_AMT\":0,\"SETL_INT_AMT\":0,\"SETL_STS\":\"N\",\"THD_BUS_ID\":\"16806870\",\"LAST_UP_TM\":1448964348000,\"LAST_UP_USR\":\"SYSTEM\",\"REMARK\":\"原还款计划ID:16806870\",\"SETL_DT\":null}]";
    }
}
